package com.yc.easy.web;

import com.yc.easy.bean.Result;
import com.yc.easy.res.DBRes;
import com.yc.easy.res.PropRes;
import lombok.Data;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.http.MediaType;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.*;

import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import javax.servlet.http.HttpSession;
import java.lang.reflect.Field;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @program: C0-S3-Ply-hwc-single
 * @description:
 * @author: 作者
 * @create: 2022-11-17 12:51
 */
@RestController("easyQueryDo")
@RequestMapping("exec/")
public class ExecDo {

    final private static Logger logger = LoggerFactory.getLogger(ExecDo.class);

    @Resource
    PropRes propRes;

    @Resource
    DBRes dbRes;

    private String loginedUserKey;

    @PostConstruct
    void init() {
        this.loginedUserKey = propRes.getProp("session.loginedUserKey");
    }

    @RequestMapping(path = "show/{id}", produces = MediaType.TEXT_HTML_VALUE + ";charset=utf-8")
    public String show(
            @PathVariable String id,
            HttpSession session,
            @RequestParam Map<String, Object> params) {
        String sql = propRes.getProp(id);
        if (sql == null) {
            return ("没有该SQL:" + id);
        } else {
            addSessionParams(session, params);
            final Sql s = Sql.build(sql, params);
            return s.toString().replaceAll("\\n", "<br>");
        }
    }

    @RequestMapping("batch")
    public Map<String, Object> batch(
            String ids,
            HttpSession session,
            @RequestParam(required = false, name = "db") String dbname,
            @RequestParam Map<String, Object> params) {
        Map<String,Object> ret = new HashMap<>();
        for (String id : ids.split("[,;，；]+")) {
            String[] items = id.split("/");
            if ("get".equals(items[0])) {
                Object res = get(items[1], session, dbname, params);
                ret.put(items[1], res);
            } else if ("list".equals(items[0])) {
                Object res = list(items[1], session, dbname, params);
                ret.put(items[1], res);
            }
        }
        return ret;
    }

    @RequestMapping("get/{id}")
    public Map<String, Object> get(
            @PathVariable String id,
            HttpSession session,
            @RequestParam(required = false, name = "db") String dbname,
            @RequestParam Map<String, Object> params) {
        String sql = propRes.getProp(id);
        if (sql == null) {
            logger.error("没有该SQL:" + id);
            return null;
        } else {
            addSessionParams(session, params);
            final Sql s = Sql.build(sql, params);
            try {
                return dbRes.getJdbc(dbname).queryForMap(s.sql, s.params);
            } catch (Exception e) {
                logger.debug("get对象错误!",e);
                return null;
            }
        }
    }

    @RequestMapping("cud/{id}")
    public Result exec(
            @PathVariable String id,
            HttpSession session,
            @RequestParam(required = false, name = "db") String dbname,
            @RequestParam Map<String, Object> params) {
        String sql = propRes.getProp(id);
        if (sql == null) {
            logger.error("没有该SQL:" + id);
            return Result.failure("没有该SQL:" + id,null);
        } else {
            addSessionParams(session, params);
            final Sql s = Sql.build(sql, params);
            try {
                if (s.sql.substring(0,6).equalsIgnoreCase("insert")) {
                    Object pk = dbRes.getJdbc(dbname).execute((ConnectionCallback<Object>) (conn) -> {
                        PreparedStatement ps = conn.prepareStatement(s.sql, PreparedStatement.RETURN_GENERATED_KEYS);
                        for (int i = 0; i < s.params.length; i++) {
                            ps.setObject(i + 1, s.params[i]);
                        }
                        int i = ps.executeUpdate();
                        ResultSet rs = ps.getGeneratedKeys();
                        if (i == 0) {
                            return 0;
                        } else if (i == 1) {
                            rs.next();
                            return rs.getObject(1);
                        } else {
                            List<Object> ids = new ArrayList<>();
                            while (rs.next()) {
                                ids.add(rs.getObject(1));
                            }
                            return ids;
                        }
                    });
                    return Result.success("新增成功!", pk);
                } else {
                    dbRes.getJdbc(dbname).update(s.sql, s.params);
                    return Result.success("修改成功!", null);
                }
            } catch (Exception e) {
                e.printStackTrace();
                return Result.success("执行失败!", null);
            }
        }
    }


    private void addSessionParams(HttpSession session, Map<String, Object> params) {
        Object loginedUser = session.getAttribute(loginedUserKey);
        if (loginedUser == null) {
            logger.debug("loginedUserKey: " + loginedUserKey + " is null");
            return;
        }
        if (loginedUser instanceof Map) {
            Map<String, ?> map = (Map<String, ?>) loginedUser;
            map.forEach((k, v) -> params.put("my_" + k, v));
        } else {
            for (Field f : loginedUser.getClass().getDeclaredFields()) {
                f.setAccessible(true);
                try {
                    params.put("my_" + f.getName(), f.get(loginedUser));
                } catch (IllegalAccessException e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }

    @RequestMapping("list/{id}")
    public List<Map<String, Object>> list(
            @PathVariable String id,
            HttpSession session,
            @RequestParam(required = false, name = "db") String dbname,
            @RequestParam Map<String, Object> params) {
        String sql = propRes.getProp(id);
        if (sql == null) {
            logger.error("没有该SQL:" + id);
            return null;
        } else {
            addSessionParams(session, params);
            logger.debug("参数集合:" + params);
            final Sql s = Sql.build(sql, params);
            return dbRes.getJdbc(dbname).queryForList(s.sql, s.params);
        }
    }

    @RequestMapping("page/{id}")
    Page page(
            @PathVariable String id,
            HttpSession session,
            Page page,
            @RequestParam(required = false, name = "db") String dbname,
            @RequestParam Map<String, Object> params) {
        String sql = propRes.getProp(id);
        if (sql == null) {
            logger.error("没有该SQL:" + id);
            return null;
        } else {
            addSessionParams(session, params);
            final JdbcTemplate jdbc = dbRes.getJdbc(dbname);
            Sql s = Sql.build("select count(*) from (" + sql + ") a", params);
            page.total = jdbc.queryForObject(s.sql, Long.class, s.params);
            s = Sql.build(sql + " limit ?, ?", params, (page.page - 1) * page.size, page.size);
            page.data = jdbc.queryForList(s.sql, s.params);
            return page;
        }
    }

    @Data
    private static class Page {
        int page = 1;
        int size = 10;
        long total;
        List<?> data;
    }

    private static class Sql {
        String sql;
        Object[] params;

        public Sql(String sql, Object[] params) {
            this.sql = sql;
            this.params = params;
        }

        @Override
        public String toString() {
            return "sql   : " + sql + "\n" +
                    "params: " + Arrays.toString(params);
        }

        private static String dynamic(String sql, Map<String, ?> params) {
            final Pattern p = Pattern.compile("\\[\\?(!?)(\\w+):([^\\]]+)\\]");
            final Matcher m = p.matcher(sql);
            StringBuffer ret = new StringBuffer();
            while (m.find()) {
                String els = m.group(1);
                String name = m.group(2);
                Object o = params.get(name);
                boolean b = o == null || o instanceof String && ((String) o).trim().isEmpty();
                if("!".equals(els)){
                    b = !b;
                }
                if (b) {
                    m.appendReplacement(ret, "");
                } else {
                    String subsql = m.group(3);
                    m.appendReplacement(ret, " " + subsql + " ");
                }
            }
            if (ret.length() == 0) {
                return sql;
            } else {
                m.appendTail(ret);
                return ret.toString();
            }
        }

        private static Sql build(String sql, Map<String, ?> params, Object... addParams) {
            sql = dynamic(sql, params);
            final Pattern p = Pattern.compile("\\?(\\w+)");
            final Matcher m = p.matcher(sql);
            List<Object> plist = new ArrayList<>();
            while (m.find()) {
                String paramName = m.group(1);
                plist.add(params.get(paramName));
            }
            // 追加的参数
            for (Object addParam : addParams) {
                plist.add(addParam);
            }
            sql = sql.replaceAll("\\?\\w+", "?").trim();
            Sql s = new Sql(sql, plist.toArray());
            logger.debug("\n" + s.toString());
            return s;
        }

//        public static void main(String[] args) {
//            String sql = "select * from article where 1=1 "+
//            "[?title: and title like concat('%',?title,'%') or content like concat('%',?title,'%')]"+
//            "[?type: and type = ?type]"+
//            "[?course_id: and course_id = ?course_id]"+
//            "order by id desc;";
//            Map<String, Object> map = new HashMap<>();
//            map.put("type", 1);
//            map.put("id", 100);
//            map.put("demo", "abc");
//            map.put("my_id", 200);
//            map.put("my_name", "root");
//            Sql build = Sql.build(sql, map);
//            System.out.println(build);
//        }
    }


}
